﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Commonlib;
namespace DAL
{
    public class OrderDAL
    {
        /// <summary>
        /// 向临时订单表中添加数据
        /// </summary>
        public string addNewOrder(Albon.Model.OrderInfo order)
        {
            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@refNo",order.refNo),
                new SqlParameter("@userName",order.userName),
                new SqlParameter("@clientName",order.clientName),
                new SqlParameter("@ret",SqlDbType.Int)
            };
            parameters[3].Direction=ParameterDirection.Output;
            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnectionString, CommandType.StoredProcedure, "p_orderadd", parameters);
            return parameters[3].Value.ToString();
        }

        //完成订单
        public bool finishOrder(Albon.Model.OrderInfo order)
        {
            SqlParameter[] parametes = new SqlParameter[] { 
                new SqlParameter("@ID",order.ID),
                new SqlParameter("@invoiceN0",order.invoiceNo),
                new SqlParameter("@shipDate",order.requiredShipDate),
                new SqlParameter("@shipMethod",order.shipMethod),
                new SqlParameter("@createDate",order.createDate)
            };
            int ret=SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnectionString, CommandType.StoredProcedure, "p_orderfinish",parametes);
            return ret > 0;
        }

        //根据查询条件返回已完成的订单视图
        public DataTable getOrder(string limit)
        {
            string cmdstr = "select * from OrderInfoView where 1=1 and "+limit;
            return SqlHelper.ExecuteDataSet(SqlHelper.SqlConnectionString, CommandType.Text, cmdstr, null).Tables[0];
        }
        //Process The Order
        public void processOrder(string orderid)
        {
            string cmdstr = "update OrderInfo set processed=1 where ID="+orderid;
            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnectionString, CommandType.Text, cmdstr, null);
        }

        //将订单的active设为0
        public bool moveToRecycleBin(string orderid)
        {
            string cmdstr = "update OrderInfo set active=0 where ID="+orderid;
            int ret=SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnectionString, CommandType.Text, cmdstr, null);
            return ret >0;
        }

        //删除订单及相关数据（彻底删除）
        public bool deleteOrder(string orderid)
        {
            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@orderID",orderid)
            };
            int ret=SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnectionString, CommandType.StoredProcedure, "p_orderdelete", parameters);
            return ret > 0;
        }

        //获取当前订单的前一个订单（相同登录用户的相同客户）
        public DataTable getPreviousOrder(string orderid)
        {
            DataTable dt = getOrder("ID="+orderid);
            string cmdstr = "select top 1 * from OrderInfo where userName='"+dt.Rows[0]["userName"].ToString()
                +"' and clientName='"+dt.Rows[0]["clientName"].ToString()+"' and ID<"+orderid+" order by ID desc";
            return SqlHelper.ExecuteDataSet(SqlHelper.SqlConnectionString,CommandType.Text,cmdstr,null).Tables[0];
        }

        //完成订单（出货）
        public void completeOrder(string orderid)
        {
            string cmdstr = "update OrderInfo set classID=3 where ID="+orderid;
            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnectionString, CommandType.Text,cmdstr, null);
        } 
        //移动订单
        public void moveOrderTo(int orderid, int classid)
        {
            string cmdstr = "update OrderInfo set classID="+classid+" where ID="+orderid;
            SqlHelper.ExecuteNonQuery(SqlHelper.SqlConnectionString, CommandType.Text, cmdstr, null);
        }
        //订单所属国家 
        public DataTable getCountry(string orderid)
        {
            string cmdstr = "select * from Country where ID in (select countryID from UserAccount where userName in (select userName from OrderInfo where ID="+orderid+"))";
            return SqlHelper.ExecuteDataSet(SqlHelper.SqlConnectionString, CommandType.Text, cmdstr, null).Tables[0];
        }


       
    }
}
